﻿-- Script Date: 30/03/2013 00:32  - ErikEJ.SqlCeScripting version 3.5.2.26
-- Database information:
-- Locale Identifier: 1033
-- Encryption Mode: 
-- Case Sensitive: False
-- Database: C:\Boulot\loki\Dev\2012\Luna\Luna.Data.SqlCE\new.sdf
-- ServerVersion: 4.0.8876.1
-- DatabaseSize: 86016
-- Created: 04/03/2013 21:58

-- User Table information:
-- Number of tables: 2
-- Company: 0 row(s)
-- Contacts: 0 row(s)

CREATE SCHEMA [Budget] AUTHORIZATION [dbo]
GO

CREATE SCHEMA [Crm] AUTHORIZATION [dbo]
GO

CREATE SCHEMA [Meta] AUTHORIZATION [dbo]
GO

CREATE TABLE [Meta].[Connectors](
[id_connector] uniqueidentifier NOT NULL ROWGUIDCOL,
[type] nvarchar(50) NOT NULL,
[login] nvarchar(100) NOT NULL,
[password] nvarchar(100) NOT NULL,
[params] nvarchar(500) NULL
);
GO
CREATE INDEX [IX_Connectors_Type] ON [Meta].[Connectors] ([type] ASC);
GO
ALTER TABLE [Meta].[Connectors] ADD CONSTRAINT [PK_Connectors] PRIMARY KEY ([id_connector]);
GO

CREATE TABLE [Meta].[References](
[id_reference] uniqueidentifier NOT NULL ROWGUIDCOL,
[last_update] datetime null,
[Google] nvarchar(32) NULL,
[Google_last_update] datetime null,
[ZohoCRM] int NULL,
[ZohoCRM_last_update] datetime null,
[from] nvarchar(50) NULL,
[Validated] bit NOT NULL
);
GO
ALTER TABLE [Meta].[References] ADD CONSTRAINT [PK_References] PRIMARY KEY ([id_reference]);
GO
CREATE INDEX [IX_References_Google] ON [Meta].[References] ([Google] ASC);
GO
CREATE INDEX [IX_References_Zoho] ON [Meta].[References] ([ZohoCRM] ASC);
GO

CREATE TABLE [Crm].[Categories] (
	[id_category] uniqueidentifier NOT NULL ROWGUIDCOL
,	[name] nvarchar(200) NOT NULL
,	[description] nvarchar(250) NULL
);
GO
CREATE TABLE [Crm].[Accounts] (
  [id_account] uniqueidentifier NOT NULL ROWGUIDCOL
, [id_category] uniqueidentifier NULL
, [name] nvarchar(200) NOT NULL
, [address] nvarchar(500) NULL
, [zipcode] nvarchar(5) NULL
, [city] nvarchar(100) NULL
, [country] nvarchar(100) NULL
, [phone_number] nvarchar(15) NULL
, [url] nvarchar(100) NULL
, [mail] nvarchar(100) NULL
, [comment] nvarchar(4000) NULL
);
GO
CREATE TABLE [Crm].[Contacts] (
  [id_contact] uniqueidentifier NOT NULL  ROWGUIDCOL
, [id_account] uniqueidentifier NOT NULL
, [name] nvarchar(100) NULL
, [given_name] nvarchar(100) NULL
, [title] nvarchar(100) NULL
, [function] nvarchar(100) NULL
, [mobile_number] nvarchar(15) NULL
, [phone_number] nvarchar(15) NULL
, [mail] nvarchar(100) NULL
, [address] nvarchar(500) NULL
, [zipcode] nvarchar(5) NULL
, [city] nvarchar(100) NULL
, [country] nvarchar(100) NULL
, [use_company_infos] bit NOT NULL
);
GO
CREATE INDEX [IX_Contacts_Account] ON [Crm].[Contacts] ([id_account] ASC);
GO
ALTER TABLE [Crm].[Categories] ADD CONSTRAINT [PK_CRM_Category] PRIMARY KEY ([id_category]);
GO
ALTER TABLE [Crm].[Accounts] ADD CONSTRAINT [PK_Accounts] PRIMARY KEY ([id_account]);
GO
ALTER TABLE [Crm].[Contacts] ADD CONSTRAINT [PK_Contacts] PRIMARY KEY ([id_contact]);
GO
ALTER TABLE [Crm].[Contacts] ADD CONSTRAINT [FK_Contacts_Accounts] FOREIGN KEY ([id_account]) REFERENCES [Crm].[Accounts]([id_account]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [Crm].[Accounts] ADD CONSTRAINT [FK_Accounts_Categories] FOREIGN KEY ([id_category]) REFERENCES [Crm].[Categories]([id_category]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

CREATE TABLE [Budget].[Accounts] (
	[id_account] uniqueidentifier NOT NULL ROWGUIDCOL
,	[name] nvarchar(100) NOT NULL
);	
GO

CREATE TABLE [Budget].[Categories] (
	[id_category] uniqueidentifier NOT NULL ROWGUIDCOL
,	[name] nvarchar(100) NOT NULL
,	[description] nvarchar(250) NULL
,	[color] nvarchar(10) NOT NULL
);
GO

CREATE TABLE [Budget].[Budgets] (
	[id_budget] uniqueidentifier NOT NULL ROWGUIDCOL
,	[id_main_budget] uniqueidentifier NOT NULL
,	[name] nvarchar(100) NOT NULL
,	[start] datetime NOT NULL
,	[end] datetime NOT NULL
,	[comment] nvarchar(500) NULL
,	[last_version] bit NOT NULL default(0)
,	[validated] bit NOT NULL default(0)
);
GO
CREATE TABLE [Budget].[Lines] (
	[id_line] uniqueidentifier NOT NULL ROWGUIDCOL
,	[id_budget] uniqueidentifier NOT NULL
,	[id_category] uniqueidentifier NULL
,	[id_parent] uniqueidentifier NULL
,	[code] nvarchar(50) NULL
,	[name] nvarchar(200) NOT NULL
,	[amount] decimal(10,2) NULL
,	[description] nvarchar(200) NOT NULL
);
GO
CREATE TABLE [Budget].[Operations] (
	[id_schedule] uniqueidentifier NOT NULL ROWGUIDCOL
,	[name] nvarchar(100) NOT NULL
);
GO
CREATE TABLE [Budget].[Flows] (
	[id_flow] uniqueidentifier NOT NULL ROWGUIDCOL
,	[name] nvarchar(100) NOT NULL
);
GO

ALTER TABLE [Budget].[Categories] ADD CONSTRAINT [PK_TRE_Category] PRIMARY KEY ([id_category]);
GO

ALTER TABLE [Budget].[Budgets] ADD CONSTRAINT [PK_TRE_Budget] PRIMARY KEY ([id_budget]);
GO

ALTER TABLE [Budget].[Lines] ADD CONSTRAINT [PK_TRE_Lines] PRIMARY KEY (id_line);
GO

ALTER TABLE [Budget].[Lines] ADD CONSTRAINT [FK_Lines_Budget] FOREIGN KEY ([id_budget]) REFERENCES [Budget].[Budgets]([id_budget]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [Budget].[Lines] ADD CONSTRAINT [FK_Lines_Category] FOREIGN KEY ([id_category]) REFERENCES [Budget].[Categories]([id_category]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
